# -*- coding:utf-8 -*-
# Created by ZhangYichao on 2018/12/03

import pymssql
import time
from GoogleTranslateAPI import GoogleTranslateAPI;


server = '192.168.2.113'  # 数据库服务器名称或IP
user = 'sa'  # 用户名
password = '12345abcdeABCDE'  # 密码
database = 'OMOP_CDM'  # 数据库名称
charset='cp936'

def getConcept(start,limit):
    conn = pymssql.connect(server, user, password, database,charset=charset)

    # conn.setencoding('utf-8')
    # conn.setdecoding(pymssql.SQL_CHAR, 'utf-8')
    # conn.setdecoding(pymssql.SQL_WCHAR, 'utf-8')
    # conn.setdecoding(pymssql.SQL_WMETADATA, 'utf-8')

    with conn:
        sql = u"select c.* from concept c order by concept_id offset %d rows fetch next %d rows only" % (start,limit)
        cursor = conn.cursor(as_dict=True)
        print("\033[0;31m" + sql + "\033[0m")
        cursor.execute(sql)
        rows =cursor.fetchall();
        indexRows=start;
        for row in rows:
            # 获得当前时间时间戳
            now = int(time.time())
            # 转换为其他日期格式,如:"%Y-%m-%d %H:%M:%S"
            timeStruct = time.localtime(now)
            strTime = time.strftime("%Y-%m-%d %H:%M:%S", timeStruct)
            indexRows=indexRows+1
            concept_id = row["concept_id"]
            concept_name = row["concept_name"]
            concept_name_zh_CN = GoogleTranslateAPI.google_translate_EtoC(concept_name)

            #print("%s,indexRows:%d,concept_name_zh_CN:%s,concept_id:%d" % (strTime,indexRows,concept_name_zh_CN, concept_id))
            try:
                updateSql = "update concept set concept_name_zh_CN='" + concept_name_zh_CN.replace("'", "''").encode(
                    'GBK') + "' where concept_id=" + str(concept_id)
                cursor.execute(updateSql)
                conn.commit()
                print("%s,indexRows:%d,UpdateSql:%s" % (strTime,indexRows, updateSql.decode("GBK").encode("utf8")))
            except:
                print("\033[0;31m %s,indexRows:%d,concept_name_zh_CN:%s,concept_id:%d Translation Failed! \033[0m" % (strTime,indexRows,concept_name_zh_CN, concept_id))


def updateConcept(concept_id,concept_name_zh_CN):
    conn = pymssql.connect(server, user, password, database)
    with conn:
        sql="update concept set concept_name_zh_CN='"+concept_name_zh_CN.encode('utf8')+"' where concept_id="+str(concept_id)
        cursor = conn.cursor()
        cursor.execute(sql)
        conn.commit()

def getConceptTotalRecord():
    conn = pymssql.connect(server, user, password, database)
    result=0
    with conn.cursor() as cursor:
        sql="SELECT COUNT (1) AS counts FROM concept "
        cursor.execute(sql)
        result=cursor.fetchone()
    return result[0];

if __name__ =='__main__':
    #getConcept(30400, 1);

    pageIndex=47
    pageSize=1000
    totalRecord =getConceptTotalRecord()
    totalPage = (totalRecord + pageSize - 1) / pageSize;
    for i in range(pageIndex,totalPage-1):
        print("\033[0;31m" + "offset:%d,next:%d" % (i*pageSize,pageSize) + "\033[0m")
        getConcept(i*pageSize,pageSize);










